import json
import os
import random
import time

import logzero
import requests
from logzero import logger

from mysql_conf import mysel_conf
from utils import get_mongo_collection, re_html_tag
import config
current_module = os.path.splitext(os.path.basename(__file__))[0]
logzero.logfile(f"/tmp/{current_module}.log", maxBytes=30_000_000, backupCount=3, encoding='utf-8')

update_used = get_mongo_collection('update_used')
logzero.logfile(f"/tmp/bilibili_update.log", maxBytes=30_000_000, backupCount=3, encoding='utf-8')
cookie = 'c472106b,1526129828,2251bb72'

headers = {
    'Cookie': config.BILIBILICOOKIE,
    'Host': 'api.vc.bilibili.com',
    'Origin': 'https://t.bilibili.com',
    'Referer': 'https://t.bilibili.com/?tab=8',
}
params = {'uid': 278341012, 'type': 8}


def get_page_info_by_num(num=3, ret=None, offset_dynamic_id=-1):
    if not ret:
        ret = []
    if not ret:
        page = requests.get('https://api.vc.bilibili.com/dynamic_svr/v1/dynamic_svr/dynamic_new',
                            params={'uid': 278341012,  "type": 8 },
                            headers=headers).json()
        page_lilst = page['data']['cards']
        print(page)
        for i in page_lilst:
            ret.append(i)
        num -= 1
        reversed(page_lilst)
        return get_page_info_by_num(num, ret, page_lilst[len(page_lilst) - 1]['desc']['dynamic_id'])
    else:
        page = requests.get('https://api.vc.bilibili.com/dynamic_svr/v1/dynamic_svr/dynamic_history',
                            params={'uid': 278341012, "offset_dynamic_id": offset_dynamic_id, "type": 8},
                            headers=headers).json()
        print(page)

        page_lilst = page['data']['cards']
        for i in page_lilst:
            ret.append(i)
        if num == 0:
            return ret
        num -= 1
        reversed(page_lilst)
        return get_page_info_by_num(num, ret, page_lilst[len(page_lilst) - 1]['desc']['dynamic_id'])


'''
    tag
'''


def get_tag(aid):
    params={'aid':aid}
    tags = requests.get(f'https://api.bilibili.com/x/tag/archive/tags',params=params).json()
    tag = []
    try:
        if len( tags['data']) <1:
            return tag
        for i in tags['data']:
            tag.append(i['tag_name'])
        return tag
    except (TypeError,KeyError) as e:
        return tag


def get_tag_id_from_mysql(tag):
    conn = mysel_conf.pool()
    cursor=conn.cursor()
    tag_id = []
    for i in tag:
        sql = "SELECT * FROM tag where name = %s"
        cursor.execute(sql, (i,))
        result = cursor.fetchone()
        if result:
            tag_id.append(result['id'])
        else:
            sql = "INSERT into tag ( name,op_time,status) VALUES ( %s, %s, %s)"
            cursor.execute(sql, (i, time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()), '0'))
            conn.commit()
            id = int(cursor.lastrowid)
            tag_id.append(id)
    cursor.close()
    conn.close()
    return tag_id
'''
     主题和源的关系
'''


def get_update_theme_and_source():
    with mysel_conf.pool().cursor() as cursor:
        # 执行sql语句，进行查询
        sql = 'SELECT theme_id,source_name  FROM theme_source_update where source_id=7 AND  theme_id in (SELECT theme_id FROM theme_auto_update where status=1)'
        cursor.execute(sql)
        # 获取查询结果
        result = cursor.fetchall()

        res = {}
        for r in result:
            res[r['source_name']] = r['theme_id']
        return res


'''
    得到用户的最新3条
'''


def get_user_list(source):
    user_list = {}
    for source_name, theme_id in source.items():
        user_list[source_name] = []
    for i in reversed(get_page_info_by_num()):
        if i['desc']['user_profile']['info']['uname'] in source:
            user_list[i['desc']['user_profile']['info']['uname']].append(i['card'])
    for i in user_list:
        user_list[i] = user_list[i][-3:]
        logger.info(user_list[i])
    return user_list


def format_to_mysql(dict_msg, theme_id):
    to_mysql = {}
    to_mysql['contents'] = {}
    to_mysql['ext'] = {}
    to_mysql['ext']['video'] = {}
    # 是转发

    media_type = 2
    title = re_html_tag(dict_msg["title"])
    to_mysql['contents']['theme_id'] = theme_id
    to_mysql['contents']['publish_time'] = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    to_mysql['contents']['dynamic_time'] = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    to_mysql['contents']['thirdparty_id'] = dict_msg["aid"]
    to_mysql['contents']['contentsType'] = '8'
    to_mysql['contents']['title'] = title
    to_mysql['contents']['media_type'] = media_type
    to_mysql['contents']['pic_link'] = f'https://m.bilibili.com/video/av{dict_msg["aid"]}.html'
    to_mysql['contents']['mongo_id'] = f'update_py_bilibili_video:aid:{dict_msg["aid"]}'

    to_mysql['ext']['video']['pic'] = dict_msg['pic']
    to_mysql['ext']['video']['big_pic'] = dict_msg['pic']
    to_mysql['ext']['video']['stream'] = dict_msg["aid"]
    return to_mysql


def insert_mysql(dict_msg, tags):
    time_str = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    conn = mysel_conf.pool()
    cursor = conn.cursor()
    try:
        sql = "INSERT INTO contents (theme_id,thirdparty_id, publish_time,title,desp,contents_refter_type,media_type,pic_link,likes_count,created_at,updated_at,dynamic_time,mongo_id) VALUES (%s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
        contents = (
            dict_msg['contents']['theme_id'],
            dict_msg['contents']['thirdparty_id'],
            dict_msg['contents']['publish_time'],
            dict_msg['contents']['title'],
            '',
            dict_msg['contents']['contentsType'],
            dict_msg['contents']['media_type'],
            dict_msg['contents']['pic_link'],
            random.randint(10, 50),
            time_str,
            time_str,
            time_str,
            dict_msg['contents']['mongo_id']
        )
        cursor.execute(sql, contents)  # 写contents
        id = int(cursor.lastrowid)
        update_used.insert({'scheme': dict_msg['contents']['thirdparty_id'], 'mysql_id': id})
        # 写入图片
        sql = "INSERT INTO contents_ext(contents_sort,created_at,updated_at,contents_id,pic_link,width,height,video_link,video_ticket,bigpic_link) value(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        contents = (
            1, time_str, time_str, id, dict_msg['ext']['video']['pic'], '0', '0',
            '',
            dict_msg['ext']['video']['stream'], dict_msg['ext']['video']['big_pic'])
        cursor.execute(sql, contents)  # 写contents

        if len(tags):
            sql = "INSERT INTO contents_tag (content_id,tag_id,name,created_at,updated_at) values(%s,%s,%s,%s,%s)"
            for tag in tags:
                logger.info(tag)
                cursor.execute(sql, (id, tag, ' ', time_str, time_str))  # 写contents
        conn.commit()  # 事务提交
    except Exception as e:
        conn.rollback()  # 事务回滚
        logger.info('事务处理失败', e)
    else:
        conn.commit()  # 事务提交
        logger.info('事务处理成功', cursor.rowcount)
    cursor.close()
    conn.close()


def main():
    try:
        source = get_update_theme_and_source()
        logger.info(source)
        get_user_video_list = get_user_list(source)
        for k, v in get_user_video_list.items():
            tag_id = []
            theme_id = source[k]
            for i in v:
                i = json.loads(i)
                with mysel_conf.pool().cursor() as cursor:
                    # 执行sql语句，进行查询
                    sql = f'SELECT * FROM contents where  pic_link like \'https://m.bilibili.com/video/av%\' and thirdparty_id=\'{i["aid"]}\''
                    cursor.execute(sql)
                    # 获取查询结果
                    result = cursor.fetchall()
                if update_used.find({'scheme': i['aid']}).count()>1:
                    logger.info('数据更新过')
                elif result:
                    logger.info(f'mysql中数据更新过,aid是{i["aid"]}')
                else:
                    format_info = format_to_mysql(i, theme_id)
                    tag_list=get_tag(i['aid'])
                    # logger.info(tag_list)
                    try:
                        if  len(tag_list) :
                            insert_mysql(format_info, get_tag_id_from_mysql(tag_list))
                            logger.info(f'成功时间：{ time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())}写入数据 aid是{ i["aid"]}')
                        else:
                            logger.info(f'时间：{ time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())}写入数据{ format_info }失败   aid是{ i["aid"]} tag{ tag}')
                    except TypeError:
                        logger.info(
                            f'时间：{ time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())}写入数据{ format_info }失败   aid是{ i["aid"]} tag{ tag_list}')

    except KeyError:
        logger.info('刷新速度太快')
        pass

if __name__ == '__main__':
    main()
